-- Copyright (c) Microsoft Corporation. All rights reserved.

/****** Object:  StoredProcedure [dbo].[prc_iiFindAddSource]    Script Date: 02/22/2007 18:26:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[prc_iiFindAddSource]
  @System NVARCHAR(20),
  @Source NVARCHAR(128),
  @Id INT OUTPUT
AS BEGIN
  SELECT @Id = Id FROM Sources WHERE System=@System and Source=@Source;
  IF @Id IS NULL BEGIN
    INSERT INTO Sources(System, Source) VALUES(@System, @Source);
    SELECT @Id = @@IDENTITY;
  END
END;
GO

/****** Object:  StoredProcedure [dbo].[prc_iiFindAddSession]    Script Date: 02/22/2007 18:27:07 ******/
CREATE PROCEDURE [dbo].[prc_iiFindAddSession]
  @LeftSystem NVARCHAR(20),
  @LeftSource NVARCHAR(128),
  @RightSystem NVARCHAR(20),
  @RightSource NVARCHAR(128)
AS BEGIN
  DECLARE @Inverse BIT;
  DECLARE @SessionId INT;
  DECLARE @LeftId INT, @RightId INT;
  DECLARE @LeftWatermark NVARCHAR(128), @RightWatermark NVARCHAR(128);

  EXECUTE prc_iiFindAddSource @LeftSystem, @LeftSource, @LeftId OUTPUT;
  EXECUTE prc_iiFindAddSource @RightSystem, @RightSource, @RightId OUTPUT;

  IF (@LeftId > @RightId) BEGIN
    DECLARE @TempId INTEGER;
    SELECT @TempId = @LeftId;
    SELECT @LeftId = @RightId;
    SELECT @RightId = @TempId;
    SELECT @Inverse = 1;
  END
  ELSE SELECT @Inverse=0;

  SELECT @SessionId = Id,
	@LeftWatermark = LeftWatermark,
	@RightWatermark = RightWatermark
  FROM Sessions 
  WHERE LeftSourceId=@LeftId AND rightSourceId=@RightId;
  
  if @SessionId IS NULL BEGIN
    INSERT INTO Sessions(LeftSourceId, RightSourceId) VALUES(@LeftId, @RightId);
    SELECT @SessionId = @@IDENTITY;
  END
  SELECT @SessionId AS SessionId, @Inverse AS Inverse, @LeftId AS LeftSourceId, @RightId AS RightSourceId,
	@LeftWatermark AS LeftWatermark, @RightWatermark AS RightWatermark;
END;
GO

/****** Object:  StoredProcedure [dbo].[prc_iiFindAddMappings]    Script Date: 03/14/2007 19:03:58 ******/
CREATE PROC [dbo].[prc_iiFindAddMappings]
  @SessionId INT,
  @SourceId INT,
  @PublicId NVARCHAR(50)
AS
BEGIN
  -- Get/create work item record
  DECLARE @SourceItemId INT;
  DECLARE @Pairs TABLE (Id INT, SourceId INT, TargetId INT, TargetPublicId NVARCHAR(50));

  SELECT @SourceItemId = Id FROM WorkItems WHERE SourceId=@SourceId AND PublicId=@PublicId;
  IF @SourceItemId IS NULL BEGIN
    INSERT INTO WorkItems(SourceId, PublicId) VALUES(@SourceId, @PublicId);
    SELECT @SourceItemId = @@IDENTITY;
  END
  ELSE BEGIN 
    -- Find all pairs
    INSERT INTO @Pairs(Id, SourceId, TargetId, TargetPublicId)
      SELECT ip.Id, ip.SourceId, ip.TargetId, twi.PublicId
        FROM ItemPairs ip INNER JOIN WorkItems twi ON ip.TargetId=twi.Id
        WHERE ip.SessionId=@SessionId AND (ip.SourceId=@SourceItemId OR ip.TargetId=@SourceItemId)
  END

  -- Return source id
  SELECT @SourceItemId As SourceId;

  -- Return pairs
  SELECT Id, SourceId, TargetId, TargetPublicId FROM @Pairs;

  -- Return synced revisions
  SELECT p.Id AS PairId, sr.SourceRev, sr.TargetRev
    FROM @Pairs p INNER JOIN SyncedRevisions sr ON p.Id = sr.PairId;
END;
GO

/****** Object:  StoredProcedure [dbo].[prc_iiPairWorkItems]    Script Date: 03/09/2007 21:49:11 ******/
CREATE PROC [dbo].[prc_iiPairWorkItems]
  @SessionId INT,
  @SrcItemId INT,
  @SrcRev INT,
  @TgtSourceId INT,
  @TgtItemPublicId NVARCHAR(50),
  @TgtRev INT
AS
BEGIN
  -- Create work item record
  DECLARE @TgtItemId INT;
  INSERT INTO WorkItems(SourceId, PublicId) VALUES(@TgtSourceId, @TgtItemPublicId);
  SELECT @TgtItemId = @@IDENTITY;

  DECLARE @SrcPairId INT, @TgtPairId INT;
  INSERT INTO ItemPairs(SessionId, SourceId, TargetId) VALUES(@SessionId, @SrcItemId, @TgtItemId);
  SELECT @SrcPairId = @@IDENTITY;

  INSERT INTO ItemPairs(SessionId, SourceId, TargetId) VALUES(@SessionId, @TgtItemId, @SrcItemId);
  SELECT @TgtPairId = @@IDENTITY;

  IF @SrcRev >= 0 BEGIN
    INSERT INTO SyncedRevisions(PairId, SourceRev, TargetRev) VALUES (@SrcPairId, @SrcRev, @TgtRev);
  END;

  SELECT @SrcPairId AS SourcePairId, @TgtPairId AS TargetPairId;
END;
GO
